import os
import pandas as pd
from openpyxl import Workbook, load_workbook

def delete_files(file_name):
    if os.path.exists(file_name):
        os.remove(file_name)
        print('文件删除成功')
    else:
        print('文件不存在')

def split_excel():
    path = r"C:\Users\Kevin\Desktop\test"
    os.chdir(path)  # 修改工作路径
    f = pd.read_excel('test1.xlsx')

    col = '三级单元'
    shape = f.shape
    print(shape)
    print(f)

    # head = f.head()
    # print(head)

    unique_set = list(set(f[col]))
    # print(unique_set)
    print(len(unique_set))
    my_list = []
    # my_list.append(f.loc[f['三级单元']=='接入维护中心'])
    # print(my_list[0])
    for i in unique_set:
        print(i)
        df = f.loc[f[col] == i]
        my_list.append(df)

    file_name = 'test2.xlsx'
    delete_files(file_name)


    # writer = pd.ExcelWriter('拆分后.xlsx', engine='xlsxwriter')

    # for i in range(len(my_list)):
    #     print(unique_set[i])
    #     my_list[i].to_excel('拆分后.xlsx',sheet_name = unique_set[i],index = False)

    # wb = Workbook()  # 在本地创建Excel工作簿
    # ws = wb.active  # 激活worksheet
    # for i in unique_set:
    #   wb.create_sheet(i)  # 在“wb”工作簿中新建一个名为"w1"的sheet,默认的位置为最后
    # ewb = load_workbook("ewb.xlsx")  # 加载本地已经存在的Excel工作簿
    # wb.save("wb.xlsx")  # 将工作簿存为

    with pd.ExcelWriter(r'test2.xlsx') as writer:
        for m in range(len(unique_set)):
            my_list[m].to_excel(writer, sheet_name=unique_set[m], index=False)

    writer._save()


split_excel()
print('----------------')










